BigQueryで新たにtime series functionsがプレビューになりました

Google Cloudデータエンジニアのはんざわです。
2024年1月27日に time series functions と range functions の2つが新たにプレビューとして追加されました。

February 27, 2024

本記事では time series functions について紹介します。

新たに追加された関数

新たに追加された関数は以下の通りです。

名前	詳細
DATE_BUCKET	dateを含むdate区間の下限を取得する
DATETIME_BUCKET	datetimeを含むdatetime区間の下限を取得する
TIMESTAMP_BUCKET	timestampを含むtimestamp区間の下限を取得する
GAP_FILL	時系列データの欠損を検出し、補完する

実際に触ってみる

それぞれの関数を実際に触ってみて、簡単に紹介したいと思います。

その前に

今回追加された新しい関数を理解するために少し数学の記号を知っておく必要があります。公式ドキュメントでもその記号が用いられているので知っておくと理解が深まると思います。

もちろん既にご存知の方は読み飛ばしてしまって構いません。

皆さんは区間で用いられる [ ] や ( )の表記や数直線上で用いられる ● や ○ といった表記をご存知でしょうか？
厳密には異なりますが、分かりやすく簡単に説明すると不等号のようなものです。
[ ]はちょうど区切りの数を含み、( )はその区切りの数を含まないことを表す記号になります。
また、数直線上では塗りつぶされた丸（●）はちょうど区切りの数を含み、空白の丸（○）はその区切りの数を含まないことを表します。
これを不等号で表現すると[ ] と ● は <= に対応し、( ) と ○ は <に対応していると考えてもらうと分かり易いと思います。
（※ 厳密には異なります）
例を挙げると、1 <= x <= 3 と [1, 3] のような感じです。

もっとの区間について知りたい方は以下のようなサイトを参考にしてみると良いかもしれません。

DATE_BUCKET

DATE_BUCKETの関数を一言で説明すると、特定の日付から指定した範囲の区間において、ある日付が属する区間の下限を取得する関数です。
この説明では意味がわからないと思いますので数直線を用いて、説明したいと思います。

数直線を用いると以下のような図になります。
特定の日付を中心に指定した範囲だけ左右に広がっていることがわかると思います。
そうした中である日付が属している区間の下限を取得するようなイメージです。

試しに特定の日付を2024-02-15とし、指定した範囲を3日と仮定してみます。
すると以下のような図になります。

この時にある日付を2024-02-13とすると2024-02-13は、2024-02-12 以上 2024-02-15 未満の区間に属します。
図からもわかる通り、この区間の下限は2024-02-12になりますので2024-02-13をDATE_BUCKET関数に適用すると2024-02-12の値が返ってきます。

以下のクエリは同様の仮定でサンプルをいくつか増やした例になります。

WITH some_dates AS (
  SELECT DATE '2024-02-10' AS my_date UNION ALL
  SELECT DATE '2024-02-11' UNION ALL
  SELECT DATE '2024-02-13' UNION ALL
  SELECT DATE '2024-02-15' UNION ALL
  SELECT DATE '2024-02-16' UNION ALL
  SELECT DATE '2024-02-19'
)

SELECT 
  my_date,
  DATE_BUCKET(my_date, INTERVAL 3 DAY, DATE '2024-2-15') AS bucket_lower_bound
FROM 
  some_dates;

> my_date   bucket_lower_bound
2024-02-10  2024-02-09
2024-02-11  2024-02-09
2024-02-13  2024-02-12
2024-02-15  2024-02-15
2024-02-16  2024-02-15
2024-02-19  2024-02-18

DATETIME_BUCKET と TIMESTAMP_BUCKET

根本的な考え方は前述したDATE_BUCKETと同じです。
異なる点としては名前からもわかる通り、区間の単位がDATEではなく、DATETIMEとTIMESTAMPになっています。

GAP_FILL

GAP_FILLは、時系列データの欠損値を補完することができます。
サンプルのテーブルを作成し、実際に触ってみます。

前準備

以下のクエリで検証用のテーブルを作成します。
欠損値を補完したいため、あえて3月7日のレコードは削除しています。

CREATE OR REPLACE TABLE sample_dataset.sales_per_day AS (
SELECT
  day,
  floor(rand() * 100) + 1 AS sales
FROM
  UNNEST(GENERATE_DATE_ARRAY('2023-01-01', '2023-12-31')) AS day
);

DELETE
FROM
  sample_dataset.sales_per_day
WHERE
  day = '2023-03-07';

実際に触ってみる

以下のクエリのように対象のテーブルと対象の時間情報を表すカラム、そのカラムの時間幅を渡す必要があります。
また、その結果からもわかる通り、削除していた3月7日のレコードがNULLで補完されています。

SELECT
  *
FROM
  GAP_FILL(
    TABLE `sample_dataset.sales_per_day`,
    ts_column => 'day',
    bucket_width => INTERVAL 1 DAY
    )
ORDER BY day

デフォルトではNULLで補完されますが、他にも前日の値（今回のケースだと3月6日のデータ）で補完したり、線形モデルの予測値で補完する方法もあるので是非利用する際にはドキュメントを確認してみてください。

参考ドキュメント： gap_fill

まとめ

今回は新たにプレビューとなったtime series functionsを紹介しました。
これらの機能が追加されることで時系列データの分析における前処理の幅が広がると思われます。
時系列データの分析を行う際には、是非活用してみてください。